package com.student.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.student.entity.StudentInfo;
import com.student.util.DBUtil;

public class StudentDao{
	private static final String URL ="jdbc:mysql://localhost:3306/test?useUnicode=true&characterEncoding=utf-8&useSSL=false";
	private static final String UNAME = "root";
	private static final String PWD = "123456";
	static int n = 1;
//查询总数据数（人数）
	public int getTotalCount(){
		String sql = "select count(1) from studentjdbc";
		return DBUtil.getTotalCount(sql);
	}
//查询当前页的内容	

	public List<StudentInfo> queryStudentByPage(int currentPage,int pageSize) {
		StudentInfo stu = null;
		List<StudentInfo> stuPage = new ArrayList<>();
		ResultSet rs = null;
		// sql = "select * from (select rownum r,t.* from (select s.* from student s order by sno asc)t where rownum<= 10*n) where r >= ((n-1)*10+1)"  
		 
		String sql = " select * from studentjdbc limit ?,?";//MySQL分页语句写法
		Object[] params ={currentPage*pageSize ,pageSize};
		
		try{
			
			// 使用	
			rs = DBUtil.queryAll(sql, params);
			
			while(rs.next()){
				
				String stuNo = rs.getString("stuNo");
				String race = rs.getString("race");
				String imageUrl = rs.getString("imageUrl");
				String raceNo = rs.getString("raceNo");
				String timeStr = rs.getString("timeStr");
				boolean status = rs.getBoolean("status");
				stu = new StudentInfo(stuNo, race, imageUrl, raceNo, timeStr, status);
				stuPage.add(stu);
			}
			return stuPage;
			
			}catch (SQLException  e) {
				e.printStackTrace();
				return null;
			}finally{
				try{
				if(rs!=null) rs.close();
				if(DBUtil.pst!=null) DBUtil.pst.close();
				if(DBUtil.con!=null) DBUtil.con.close();
				}catch(SQLException e){
					e.printStackTrace();
				}
			}
	}
	
//判断学生是否存在
	public boolean isExist(String stuNo){
		//调用本类的方法   //根据学号  查询学生
		return this.queryStudentBySno(stuNo)==null? false:true;
	}
//报名添加学生	
	public boolean addStudent(StudentInfo stu){
		//序号，参赛号码(赛号+序号 )，学号，省份证，报名时间，签到状态。
		String[] races = {"1000m长跑","400m短跑","立定跳远","铁人三项"};
		String id = "00"+n;
		String raceNo = null;
		
		for(int i=0;i<races.length;i++){
		if(races[i].equals(stu.getRace())){
		raceNo ="00"+i+ id;
		}
		}n++;
		Date timeStr = new Date();
		
		String sql = "insert into studentjdbc values(?,?,?,?,?,?)";
		Object[] params ={id,raceNo,stu.getStuNo(),stu.getImageUrl(),timeStr.toString(),stu.isStatus()};
		return DBUtil.executeUpdate(sql, params);
		
	}
//根据学号  修改学生签到状态	
	public boolean updateStudentStatusBySno(String stuNo){
		String sql = "update studentjdbc set status=true ,where stuNo = ? ";
		Object[] params = {stuNo};
		return DBUtil.executeUpdate(sql, params);
	}
	
	
//根据学号 删除学生	
	public boolean deleteStudentBySno(int sno){
		String sql = "delete from studentjdbc where sno=?";
		Object[] params = {sno};
		return DBUtil.executeUpdate(sql, params);
	} 
	
//查询全部学生报名信息
	public List<StudentInfo> queryAll(){
		String sql = "select * from studentjdbc";
		StudentInfo stu = null;
		ResultSet rs = null;
		List<StudentInfo> stuall = new ArrayList<>();
		
		try{
		
		// 使用	
		rs = DBUtil.queryAll(sql, null);
		
		while(rs.next()){
			
			String stuNo = rs.getString("stuNo");
			String race = rs.getString("race");
			String imageUrl = rs.getString("imageUrl");
			String raceNo = rs.getString("raceNo");
			String timeStr = rs.getString("timeStr");
			boolean status = rs.getBoolean("status");
			
			stu = new StudentInfo(stuNo, race, imageUrl, raceNo, timeStr, status);
			stuall.add(stu);
		}
		return stuall;
		
		}catch (SQLException  e) {
			e.printStackTrace();
			return null;
		}finally{
			try{
			if(rs!=null) rs.close();
			if(DBUtil.pst!=null) DBUtil.pst.close();
			if(DBUtil.con!=null) DBUtil.con.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
	}
	
//根据学号  查询学生	
	public StudentInfo queryStudentBySno(String stuNo){
		Connection con = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		StudentInfo stu = null;
		
		try{
		Class.forName("com.mysql.jdbc.Driver");
		con = DriverManager.getConnection(URL, UNAME, PWD);
		String sql = "select * from studentjdbc where sno=?";
		pst = con.prepareStatement(sql);
		pst.setString(1, stuNo);
		rs = pst.executeQuery();
		
		if(rs.next()){
			
			String race = rs.getString("race");
			String imageUrl = rs.getString("imageUrl");
			stu = new StudentInfo(stuNo, race, imageUrl);
		}
		return stu;
		
		}catch( ClassNotFoundException e){
			e.printStackTrace();
			return null;
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally{
			try{
			if(rs!=null) rs.close();
			if(pst!=null) pst.close();
			if(con!=null) con.close();
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		
	}
}
